/**
 * 
 */
package com.td.framework.webmanager.ryjbxx.dao.impl;

import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.Transaction;
import org.springframework.orm.hibernate3.support.HibernateDaoSupport;

import com.td.framework.common.Constant;
import com.td.framework.webmanager.common.MD5;
import com.td.framework.webmanager.domain.AppsysDlyh;
import com.td.framework.webmanager.domain.AppsysDmb;
import com.td.framework.webmanager.domain.AppsysRybmgx;
import com.td.framework.webmanager.domain.AppsysRyjbxx;
import com.td.framework.webmanager.ryjbxx.dao.RyjbxxDao;

/**
 * 
 * Title: 人员信息处理数据层实现类 Description:人员信息处理 Copyright: Copyright (c) 2008 Company
 * 北京水软科技有限公司
 * 
 * @author FLT006
 * @version 1.0
 */
public class RyjbxxDaoImpl extends HibernateDaoSupport implements RyjbxxDao {
	private static final Log log = LogFactory.getLog(RyjbxxDaoImpl.class);
	private String sql = "";// sql语句;
	private boolean delStatus;// 删除记录标志 true:彻底删除；false:象征性删除
	private List<AppsysRyjbxx> list = null;// 人员信息list;
	private boolean flag = false;// 判断字段;

	/**
	 * @toDo:删除人员信息,支持批量删除;
	 * @param rybhs,bmbh
	 * @return boolean;
	 */
	public boolean deleteAppsysRyjbxx(String[] rybhs, String bmbh) {
		// TODO Auto-generated method stub
		Connection con = null;
		Session session = this.getSession();
		try {
			con = session.connection();
			con.setAutoCommit(false);
			Statement st = con.createStatement();
			// PreparedStatement pstmt = con.prepareStatement(sql);
			for (int i = 0; i < rybhs.length; i++) {
				if (delStatus) {
					// 删除对应的权限关系
					/*sql = "delete from appsys_qxgx t  where t.qxbh in "
							+ " ( select t.qxbh from appsys_yhzqx t where t.flag=1 "
							+ "and t.yhzbh in (select t.yhbh from appsys_dlyh t "
							+ "where t.rybh=" + rybhs[i] + " ))";
					st.addBatch(sql);
					// 删除对应的页面功能
					sql = "delete from appsys_qxgns t  where t.qxbh in "
							+ " ( select t.qxbh from appsys_yhzqx t where t.flag=1 "
							+ "and t.yhzbh in (select t.yhbh from appsys_dlyh t "
							+ "where t.rybh=" + rybhs[i] + " ))";
					st.addBatch(sql);
					// 删除对应的权限编号
					sql = "delete from appsys_qxxx t  where t.qxbh in "
							+ " ( select t.qxbh from appsys_yhzqx t where t.flag=1 "
							+ "and t.yhzbh in (select t.yhbh from appsys_dlyh t "
							+ "where t.rybh=" + rybhs[i] + " ))";
					//st.addBatch(sql);
					// 删除对应的用户、权限关系
					sql = " delete from appsys_yhzqx t where t.flag=1 "
							+ "and t.yhzbh in (select t.yhbh from appsys_dlyh t "
							+ "where t.rybh=" + rybhs[i] + " )";
					//st.addBatch(sql);
*/					// 删除对应的用户、用户组关系
					sql = "delete from appsys_yhyhzgx where yhbh in "
							+ "(select yhbh from appsys_dlyh "
							+ "where rybh=" + rybhs[i] + " )";
					st.addBatch(sql);
					// 删除对应的用户
					sql = "delete from appsys_dlyh where  rybh="
							+ rybhs[i];
					st.addBatch(sql);
					// 删除人员
					sql = "delete from appsys_ryjbxx where rybh="
							+ rybhs[i];
					st.addBatch(sql);
					// 删除人员、部门关系
					sql = "delete from appsys_rybmgx where rybh="
							+ rybhs[i];
					st.addBatch(sql);
				} else {
					/*// 删除对应的权限关系
					sql = "update appsys_qxgx t set t.scbz=1 where t.qxbh in "
							+ " ( select t.qxbh from appsys_yhzqx t where t.flag=1 "
							+ "and t.yhzbh in (select t.yhbh from appsys_dlyh t "
							+ "where t.rybh=" + rybhs[i] + " ))";
					//st.addBatch(sql);
					// 删除对应的页面功能
					sql = "update appsys_qxgns t set t.scbz=1 where t.qxbh in "
							+ " ( select t.qxbh from appsys_yhzqx t where t.flag=1 "
							+ "and t.yhzbh in (select t.yhbh from appsys_dlyh t "
							+ "where t.rybh=" + rybhs[i] + " ))";
					//st.addBatch(sql);
					// 删除对应的权限编号
					sql = "update appsys_qxxx t set t.scbz=1 where t.qxbh in "
							+ " ( select t.qxbh from appsys_yhzqx t where t.flag=1 "
							+ "and t.yhzbh in (select t.yhbh from appsys_dlyh t "
							+ "where t.rybh=" + rybhs[i] + " ))";
					//st.addBatch(sql);
					// 删除对应的用户、权限关系
					sql = " update appsys_yhzqx t set t.scbz=1 where t.flag=1 "
							+ "and t.yhzbh in (select t.yhbh from appsys_dlyh t "
							+ "where t.rybh=" + rybhs[i] + " )";
					//st.addBatch(sql);
*/					// 删除对应的用户、用户组关系
					sql = "update appsys_yhyhzgx t set t.scbz=1 where t.yhbh in "
							+ "(select t.yhbh from appsys_dlyh t "
							+ "where t.rybh=" + rybhs[i] + " )";
					st.addBatch(sql);
					// 删除对应的用户
					sql = "update appsys_dlyh a set a.scbz=1 where  a.rybh="
							+ rybhs[i];
					st.addBatch(sql);
					// 删除人员
					sql = "update appsys_ryjbxx a  set a.scbz=1 where  a.rybh="
							+ rybhs[i];
					st.addBatch(sql);
					// 删除人员、部门关系
					sql = "update appsys_rybmgx b  set b.scbz=1 where  b.rybh="
							+ rybhs[i];
					st.addBatch(sql);
				}
			}
			st.executeBatch();
			con.commit();
			//con.setAutoCommit(true);
			flag = true;
		} catch (SQLException e) {
			log.error("事务退回原因",e);
			// TODO Auto-generated catch block
			try {
				con.rollback();
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				log.error("Exception： RyjbxxDaoImpl+deleteAppsysRyjbxx", e);
			}
			flag = false;
		} finally {
			try {
				con.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				flag = true;
				log.error("Exception： RyjbxxDaoImpl+deleteAppsysRyjbxx", e);
			}
			////session.close();
		}
		return flag;
	}

	/**
	 * @toDo:得到总记录数;
	 * @return rows;
	 */
	@SuppressWarnings("unchecked")
	public int getRows() {
		int rows = 0;
		Session session = getSession();
		SQLQuery query = session.createSQLQuery(sql).addEntity(
				AppsysRyjbxx.class);
		List<AppsysRyjbxx> list = query.list();
		//session.close();
		if (!list.isEmpty()) {
			rows = list.size();
		}
		return rows;
	}

	/**
	 * @toDo: 通过部门编号查询人员信息;
	 * @param pageSize,startRow
	 * @return list;
	 */
	@SuppressWarnings("unchecked")
	public List<AppsysRyjbxx> findBybmbh(int pageSize, int startRow) {
		// TODO Auto-generated method stub
		Session session = getSession();
		SQLQuery query = session.createSQLQuery(sql).addEntity(
				AppsysRyjbxx.class);
		query.setFirstResult(startRow);
		query.setMaxResults(pageSize);
		List<AppsysRyjbxx> list = query.list();
		//session.close();
		return list;
	}

	/**
	 * @toDo: 通过部门编号查询人员信息;
	 * @param bmbh,type
	 * @return list;
	 */
	@SuppressWarnings("unchecked")
	public List<AppsysRyjbxx> findBybmbhs(String bmbh ,int type) {
		// TODO Auto-generated method stub
		Session session = getSession();
		sql = "select t.* from appsys_ryjbxx t ,appsys_rybmgx a ,appsys_bmxx b  "
				+ " where t.scbz=0 and t.rybh = a.rybh and a.scbz=0 and b.bmbh = a.bmbh ";
		if (!bmbh.equals("")) {
			if(type==0)
			{
				sql += " and b.bmbh like '" + bmbh + "%' ";
				sql += " order by b.ccs,b.pxh,a.bmbh,t.pxh ";
			}
			else
			{
				sql += " and b.bmbh = '" + bmbh + "' ";
				sql += " order by t.pxh ";
			}
			
		}
		SQLQuery query = session.createSQLQuery(sql).addEntity(
				AppsysRyjbxx.class);
		List<AppsysRyjbxx> list = query.list();		
		return list;
	}

	/**
	 * @toDo:通过人员编号查询人员信息;
	 * @param rybh
	 * @return AppsysRyjbxx;
	 */
	public AppsysRyjbxx getAppsysRyjbxx(Long rybh) {
		// TODO Auto-generated method stub
		// 此处由于有的appsys_dlyh中的id 和 rybh 不统一
		AppsysRyjbxx ryjbxx = new AppsysRyjbxx();
		if(this.getHibernateTemplate().get(AppsysRyjbxx.class,rybh)==null){
			AppsysDlyh dlyh = (AppsysDlyh) this.getHibernateTemplate().get(
					AppsysDlyh.class, rybh);	
			ryjbxx = (AppsysRyjbxx) this.getHibernateTemplate().get(AppsysRyjbxx.class,dlyh.getRybh());
		}else{
			ryjbxx = (AppsysRyjbxx) this.getHibernateTemplate().get(AppsysRyjbxx.class,rybh);
		}
		
		return  ryjbxx;
	}

	/**
	 * @toDo:保存人员信息,用户名;
	 * @param appsysRyjbxx,appsysRybmgx,appsysDlyh
	 * @return boolean;
	 */
	public boolean addAppsysRyjbxx(AppsysRyjbxx appsysRyjbxx,
			AppsysRybmgx appsysRybmgx, AppsysDlyh appsysDlyh) {
		// TODO Auto-generated method stub
		try {
			Session session = getSession();
			//Transaction t = session.beginTransaction();
			session.save(appsysRyjbxx);
			appsysRybmgx.setRybh(appsysRyjbxx.getRybh());
			session.save(appsysRybmgx);
			// 保存用户信息
			if (!appsysDlyh.getYhm().equals("")) {
				MD5 md5 = new MD5();
				appsysDlyh
						.setYhbh(getNextYhbh(appsysRybmgx.getBmbh(), session));
				appsysDlyh.setMima(md5.getMD5ofStr(appsysDlyh.getMima()));
				appsysDlyh.setRybh(appsysRyjbxx.getRybh());
				appsysDlyh.setScbz(new Long(0));
				session.save(appsysDlyh);
			}
			//t.commit();
			//session.close();
			flag = true;
		} catch (RuntimeException re) {
			flag = false;
			log.error("Exception： RyjbxxDaoImpl+addAppsysRyjbxx", re);
			throw re;
		}
		return flag;
	}

	/**
	 * @toDo:产生用户编号; 用户编码由10位部门编号+4位流水号组成;
	 * @param bmbh
	 * @param session
	 * @return yhbh;
	 */
	public String getNextYhbh(String bmbh, Session session) {
		String yhbh = "";// 用户编号;
		sql = "select max(t.yhbh) from com.td.framework.webmanager.domain.AppsysDlyh t ,"
				+ "com.td.framework.webmanager.domain.AppsysBmxx a "
				+ "where a.bmbh='"
				+ bmbh
				+ "' and substr(t.yhbh,0,10)=a.bmgdbh " + " order by t.yhbh ";
		yhbh = (String) session.createQuery(sql).uniqueResult();
		// 首次产生用户编号;
		if (yhbh == null) {
			sql = "select t.bmgdbh from com.td.framework.webmanager.domain.AppsysBmxx t"
					+ " where t.bmbh='" + bmbh + "'";
			yhbh = session.createQuery(sql).uniqueResult() + "0001";
		}
		// 产生下一个编号;
		else {
			String num = (Integer.parseInt(yhbh.substring(10)) + 1) + "";
			for (int i = num.length(); i < 4; i++) {
				num = "0" + num;
			}
			yhbh = yhbh.substring(0, 10) + num;
		}
		return yhbh;
	}

	/**
	 * @toDo:更新人员信息;
	 * @param appsysRyjbxx,bmbh
	 * @return boolean;
	 */
	public boolean updateAppsysRyjbxx(AppsysRyjbxx appsysRyjbxx, String bmbh) {
		// TODO Auto-generated method stub
		try {
			Session session = getSession();
			//Transaction t = session.beginTransaction();

			sql = "from com.td.framework.webmanager."
					+ "domain.AppsysRybmgx a where a.scbz=0 and a.rybh = "
					+ appsysRyjbxx.getRybh();
			AppsysRybmgx appsysRybmgx = (AppsysRybmgx) session.createQuery(sql)
					.list().get(0);
			appsysRybmgx.setBmbh(bmbh);

			session.update(appsysRyjbxx);
			session.update(appsysRybmgx);
			//t.commit();
			//session.close();
			flag = true;
		} catch (RuntimeException re) {
			flag = false;
			log.error("Exception： RyjbxxDaoImpl+updateAppsysRyjbxx", re);
			throw re;
		}
		return flag;
	}
	/**
	 * @toDo:更新人员排序;
	 * @param rybhs
	 * @param pxh
	 * @return boolean;
	 */
	public boolean orderAppsysRyjbxx(String[] rybhs,String[] pxh)
	{
		boolean flag = false;
		Connection con = null;
		try {
			con = getSession().connection();
			con.setAutoCommit(false);
			Statement st = con.createStatement();
			for(int i=0;i<rybhs.length;i++)
			{
				sql = "update appsys_ryjbxx t set t.pxh="+pxh[i]
				+" where t.rybh = "+rybhs[i];
				st.addBatch(sql);
			}
			st.executeBatch();
			con.commit();
			con.setAutoCommit(true);
			flag = true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			flag = false;
			log.error("Exception： RyjbxxDaoImpl+orderAppsysRyjbxx", e);
		} finally {
			if (con != null) {
				try {
					con.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					flag = false;
					log.error("Exception： RyjbxxDaoImpl+orderAppsysRyjbxx", e);
				}
			}
		}
		return flag;
	}
	/**
	 * @toDo:通过人员编号查询部门编号;
	 * @param rybh
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public String getBmbh(Long rybh) {
		String bmbh = "";
		sql = "select t.bmbh from com.td.framework."
				+ "webmanager.domain.AppsysRybmgx t where t.rybh =" + rybh;
		List list = this.getHibernateTemplate().find(sql);
		if (!list.isEmpty()) {
			bmbh = (String) list.get(0);
		}
		return bmbh;
	}

	/**
	 * @toDo:通过代码类型查询代码信息;
	 * @param dmlx
	 * @return list
	 */
	@SuppressWarnings("unchecked")
	public List<AppsysDmb> findDmbByDmlx(String dmlx) {		
		sql = "from com.td.framework.webmanager.domain.AppsysDmb t "
					+ "where t.dmlx='"+dmlx+"'";		
		List<AppsysDmb> list = this.getHibernateTemplate().find(sql);
		return list;
	}

	/**
	 * @toDo:get删除记录标志;
	 * @return the delStatus
	 */
	public boolean isDelStatus() {
		return delStatus;
	}

	/**
	 * @toDo:set删除记录标志;
	 * @param delStatus
	 *            the delStatus to set
	 */
	public void setDelStatus(boolean delStatus) {
		this.delStatus = delStatus;
	}

	/**
	 * @toDo:getsql语句;
	 * @return the sql
	 */
	public String getSql() {
		return sql;
	}

	/**
	 * @toDo:setsql语句;
	 * @param sql
	 *            the sql to set
	 */
	public void setSql(String bmbh, String xm, String zw,String yhbh,String defaultBmbh,String xtdm) {
		sql = "select t.* from appsys_ryjbxx t ,appsys_rybmgx a ,appsys_bmxx b  "
				+ " where t.scbz=0 and t.rybh = a.rybh and a.scbz=0 and b.bmbh = a.bmbh ";
		if(!yhbh.equals("-1"))
		{			
		}
		if (!xm.equals("")) {
			sql += " and t.xm like '%" + xm + "%' ";
		}
		if (!zw.equals("")) {
			sql += " and t.zw like '%" + zw + "%' ";
		}
		if (!bmbh.equals("")&&!bmbh.equals("-1")) {
			sql += " and b.bmbh = '" + bmbh + "' ";
		}
		sql += " order by b.ccs,b.pxh,a.bmbh,t.pxh ";
	}
	
	/**
	 * @ toDo:检查用户名是否重复
	 * @return boolean true 为通过，false为不通过
	 */
	public boolean checkYhm(String yhm){
		boolean flag = false;
		 int index = yhm.indexOf(":");
		 String hql = "";
		 if(index<=0){
		   hql = "from AppsysDlyh where yhm = '"+yhm+"'";
		 }else{
			String yhbh = yhm.split(":")[1].toString();
			String  yhmc = yhm.split(":")[0].toString();
			hql = "from AppsysDlyh where yhm = '"+yhm+"' and yhbh!='"+yhbh+"'";
		 }
		int size = this.getHibernateTemplate().find(hql).size();
		if(size<=0){
			flag = true;
		}
		return flag;
	}
}
